﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class FunctionDAL
    {
        private const string SQL_SELECT_WS_FUNCTION = "select FUN_FUNCTION_ID,FUN_FUNCTION_NAME,FUN_MODULE_ID,FUN_SORT_INDEX from WS_FUNCTION where 1=1";
        private const string SQL_SELECT_WS_FUNCTION_AND_ROLE = "select FUN_FUNCTION_ID,FUN_FUNCTION_NAME,FUN_MODULE_ID,FUN_SORT_INDEX from WS_FUNCTION,WS_ROLE_FUNCTION where ROF_FUNCTION_ID=FUN_FUNCTION_ID";
        private const string SQL_INSERT_WS_FUNCTION = "insert into WS_FUNCTION(FUN_FUNCTION_ID,FUN_FUNCTION_NAME,FUN_MODULE_ID,FUN_SORT_INDEX) values(@FunctionId,@FunctionName,@ModuleId,@SortIndex)";
        private const string SQL_UPDATE_WS_FUNCTION = "update WS_FUNCTION set FUN_FUNCTION_NAME=@FunctionName,FUN_MODULE_ID=@ModuleId,FUN_SORT_INDEX=@SortIndex where 1=1 ";

        private const string SQL_WHERE_FUNCTION_ID = " and FUN_FUNCTION_ID=@FunctionId ";
        private const string SQL_WHERE_ROLE_ID = " and ROF_ROLE_ID=@RoleId ";
        private const string SQL_WHERE_MODULE_ID = " and FUN_MODULE_ID=@ModuleId ";

        private const string SQL_ORDER_BY_SORT_INDEX = " order by FUN_SORT_INDEX ";
        private const string SQL_ORDER_BY_MODULE_ID_AND_SORT_INDEX = " order by FUN_MODULE_ID,FUN_SORT_INDEX ";

        private const string PARM_FUNCTION_ID = "@FunctionId";
        private const string PARM_FUNCTION_NAME = "@FunctionName";
        private const string PARM_MODULE_ID = "@ModuleId";
        private const string PARM_SORT_INDEX = "@SortIndex";
        private const string PARM_ROLE_ID = "@RoleId";

        private static FunctionDAL instance = new FunctionDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static FunctionDAL Instance
        {
            get
            {
                return instance;
            }
        }

        public FunctionModel Get(string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_FUNCTION);
            sbSQL.Append(SQL_WHERE_FUNCTION_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_FUNCTION_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            using (DataSet ds = MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray()))
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow t = ds.Tables[0].Rows[0];
                    return new FunctionModel()
                    {
                        Id = Convert.ToString(t.ItemArray[0]),
                        Name = Convert.ToString(t.ItemArray[1]),
                        ModuleId = Convert.ToString(t.ItemArray[2]),
                        SortIndex = Convert.ToInt32(t.ItemArray[3])
                    };
                }
                return null;
            }
        }
        public List<FunctionModel> Gets()
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_FUNCTION);
            sbSQL.Append(SQL_ORDER_BY_MODULE_ID_AND_SORT_INDEX);

            using (DataSet ds = MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), null))
            {
                DataTable dt = ds.Tables[0];
                List<FunctionModel> list = dt.AsEnumerable().Select(t => new FunctionModel
                {
                    Id = Convert.ToString(t.ItemArray[0]),
                    Name = Convert.ToString(t.ItemArray[1]),
                    ModuleId = Convert.ToString(t.ItemArray[2]),
                    SortIndex = Convert.ToInt32(t.ItemArray[3])
                }).ToList<FunctionModel>();

                return list;
            }
        }
        public List<FunctionModel> Gets(string moduleId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_FUNCTION);
            sbSQL.Append(SQL_WHERE_MODULE_ID);
            sbSQL.Append(SQL_ORDER_BY_SORT_INDEX);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_MODULE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = moduleId });

            using (DataSet ds = MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray()))
            {
                DataTable dt = ds.Tables[0];
                List<FunctionModel> list = dt.AsEnumerable().Select(t => new FunctionModel
                {
                    Id = Convert.ToString(t.ItemArray[0]),
                    Name = Convert.ToString(t.ItemArray[1]),
                    ModuleId = Convert.ToString(t.ItemArray[2]),
                    SortIndex = Convert.ToInt32(t.ItemArray[3])
                }).ToList<FunctionModel>();

                return list;
            }
        }
        public List<FunctionModel> GetFromRole(string roleId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_FUNCTION_AND_ROLE);
            sbSQL.Append(SQL_WHERE_ROLE_ID);
            sbSQL.Append(SQL_ORDER_BY_SORT_INDEX);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ROLE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = roleId });

            using (DataSet ds = MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray()))
            {
                DataTable dt = ds.Tables[0];
                List<FunctionModel> list = dt.AsEnumerable().Select(t => new FunctionModel
                {
                    Id = Convert.ToString(t.ItemArray[0]),
                    Name = Convert.ToString(t.ItemArray[1]),
                    ModuleId = Convert.ToString(t.ItemArray[2]),
                    SortIndex = Convert.ToInt32(t.ItemArray[3])
                }).ToList<FunctionModel>();

                return list;
            }
        }
        public int Insert(DbTransaction trans, FunctionModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_FUNCTION_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_FUNCTION_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Name });
            parms.Add(new MySqlParameter(PARM_MODULE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.ModuleId });
            parms.Add(new MySqlParameter(PARM_SORT_INDEX, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = model.SortIndex });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_FUNCTION, parms.ToArray());
        }
        public int Update(DbTransaction trans, FunctionModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_UPDATE_WS_FUNCTION);
            sbSQL.Append(SQL_WHERE_FUNCTION_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_FUNCTION_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_FUNCTION_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Name });
            parms.Add(new MySqlParameter(PARM_MODULE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.ModuleId });
            parms.Add(new MySqlParameter(PARM_SORT_INDEX, MySqlDbType.Int32) { Direction = ParameterDirection.Input, Value = model.SortIndex });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
    }
}
